﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Dynamic;
using System.IO;

namespace RegisterProvider
{
    public class RegisterDataBaseConnector : IRegister
    {
        const string DataBaseName = "ITEMSREGISTER.sdf";
        private const string ItemsRegisterTableName = "ITEMSREGISTER";

        private static string ConnectionString
        {
            get
            {
                return String.Format("Data Source = {0}", DataBaseName);
            }
        }

        public const string IdentColumnName = "Ident";
        public const string PriceColumnName = "Price";
        private readonly SqlCeConnection _connection = new SqlCeConnection(ConnectionString);

        /// <summary>
        /// Создаем таблицу, если она не существует с колонками price и ident
        /// </summary>
        public void CreateRegisterTableIfNotExists()
        {
            if (!TableExists(_connection, ItemsRegisterTableName))
            {
                _connection.Open();
                var sql = String.Format("CREATE TABLE {0} (IDENT NVARCHAR(255), PRICE INT)", ItemsRegisterTableName);
                var cmd = new SqlCeCommand(sql, _connection);
                cmd.ExecuteNonQuery();
                _connection.Close();
            }
        }

        /// <summary>
        /// Создаем базу, если ее нет
        /// http://arcanecode.com/2007/01/25/create-a-sql-server-compact-edition-database-with-c/
        /// </summary>
        public void CreateDataBaseIfNotExists()
        {
            if (File.Exists(DataBaseName))
                return;
            var en = new SqlCeEngine(ConnectionString);
            en.CreateDatabase();
        }

        /// <summary>
        /// Проверяет существует ли выбранная таблица
        /// </summary>
        /// <param name="connection">подключение к базе данных SQL Server</param>
        /// <param name="tableName">имя таблицы</param>
        /// <returns></returns>
        public bool TableExists(SqlCeConnection connection, string tableName)
        {
            _connection.Open();
            using (var command = new SqlCeCommand())
            {
                command.Connection = connection;
                var sql = string.Format(
                        "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '{0}'",
                         tableName);
                command.CommandText = sql;
                var count = Convert.ToInt32(command.ExecuteScalar());
                _connection.Close();
                return (count > 0);
            }
        }

        public DataTable RegisterTable
        {
            get;
            set;
        }

        public RegisterDataBaseConnector()
        {
            Load();
        }

        /// <summary>
        /// Записывает в таблицу данные из БД
        /// </summary>
        public DataTable Load()
        {
            RegisterTable = new DataTable();
            RegisterTable.Columns.Add(new DataColumn(IdentColumnName, Type.GetType("System.String")));
            RegisterTable.Columns.Add(new DataColumn(PriceColumnName, Type.GetType("System.Int32")));
            RegisterTable.PrimaryKey = new DataColumn[] { RegisterTable.Columns[IdentColumnName] };

            CreateDataBaseIfNotExists();
            CreateRegisterTableIfNotExists();

            _connection.Open();
            var sqlceCommand = new SqlCeCommand
                {
                    CommandText = String.Format("SELECT * FROM {0}", ItemsRegisterTableName),
                    Connection = _connection
                };
            var reader = sqlceCommand.ExecuteReader();
            while (reader.Read())
            {
                var newRow = RegisterTable.NewRow();
                newRow[IdentColumnName] = reader.GetString(0);
                newRow[PriceColumnName] = reader.GetInt32(1);
                RegisterTable.Rows.Add(newRow);
            }
            RegisterTable.AcceptChanges();
            _connection.Close();
            return RegisterTable;
        }

        /// <summary>
        /// Записывает содержимое таблицы в локальную БД.
        /// </summary>
        public void ApplyChanges()
        {
            _connection.Open();

            foreach (DataRow row in RegisterTable.Rows)
            {
                if (row.RowState != DataRowState.Added)
                    continue;
                var ident = row[IdentColumnName];
                var price = row[PriceColumnName];
                var sqlceCommand = new SqlCeCommand
                    {
                        Connection = _connection,
                        CommandText = String.Format("INSERT INTO {0} VALUES ('" + ident + "', '" + price + "')", ItemsRegisterTableName),
                    };

                sqlceCommand.ExecuteNonQuery();
            }

            foreach (DataRow row in RegisterTable.Rows)
            {
                if (row.RowState != DataRowState.Deleted)
                    continue;
                var ident = row[IdentColumnName, DataRowVersion.Original];
                var sqlceCommand = new SqlCeCommand
                {
                    Connection = _connection,
                    CommandText = String.Format("DELETE FROM {0} WHERE IDENT = '" + ident + "'", ItemsRegisterTableName)
                };

                sqlceCommand.ExecuteNonQuery();
            }

            _connection.Close();
        }
    }
}
